function timestamp2time(unix_timestamp, format) {
    return Qt.formatDateTime(new Date(unix_timestamp * 1000), format);
}


function openCache() {
    return openDatabaseSync("coscup2011", "1.0", "API Data", 10000);
}


function init() {
    var db = openCache();
    db.transaction(
                function(tx) {
                    tx.executeSql("CREATE TABLE IF NOT EXISTS sessions(abstract TEXT, bio TEXT, from_ INTEGER, id INTEGER, isBreak TEXT, lang TEXT, name TEXT, room INTEGER, speaker TEXT, speakerTitle TEXT, to_ INTEGER, type INTEGER, isStar INTEGER)");
                    tx.executeSql("CREATE TABLE IF NOT EXISTS types(type TEXT)");
                    tx.executeSql("CREATE TABLE IF NOT EXISTS rooms(room TEXT)");
                    tx.executeSql("CREATE TABLE IF NOT EXISTS sponsors(level TEXT, desc TEXT, logoUrl TEXT, name TEXT, url TEXT)");
                    //console.log("init type");
                }

                )
}

function getFavoriteCache(session) {
    var db = openCache();
    var res = "Unknown";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM sessions;");
                    if (rs.rows.length > 0) {
                        for (var i = 0; i < rs.rows.length; i++) {
                            var from = timestamp2time(rs.rows.item(i).from_, "hh:mm");
                            var to = timestamp2time(rs.rows.item(i).to_, "hh:mm");
                            var day = timestamp2time(rs.rows.item(i).from_, "dddd");

                            if (rs.rows.item(i).isStar == 1) {
                                var starIcon = "select"
                            } else {
                                starIcon = "un-select"
                            }

                            if (rs.rows.item(i).isStar == 1) {
                                session.append({
                                               "name": rs.rows.item(i).name,
                                               "speaker": rs.rows.item(i).speaker,
                                               "from": from,
                                               "to": to,
                                               "day": day,
                                               "room": getRoomCache(rs.rows.item(i).room),
                                               "starIcon": starIcon,
                                               "id": rs.rows.item(i).id
                            })
                            }
                        }
                        res = "OK";
                    }
                }

                )
    return res;
}

function setFavoriteCache(id, star) {
    var db = openCache();
    var res = "Error";
    db.transaction(
                function(tx) {
                        var rs = tx.executeSql("SELECT * FROM sessions");
                    if (rs.rows.length > 0) {
                                rs = tx.executeSql("UPDATE sessions SET isStar=? WHERE id=?;",[star, rs.rows.item(id).id]);

                        res = "OK";
                    }
                }

                )
    return res;
}

function getSessionScheduleCache(day, session) {
    var db = openCache();
    var res = "Unknown";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM sessions");
                    if (rs.rows.length > 0) {
                        for (var i = 0; i < rs.rows.length; i++) {
                            var from = timestamp2time(rs.rows.item(i).from_, "hh:mm");
                            var to = timestamp2time(rs.rows.item(i).to_, "hh:mm");
                            if (rs.rows.item(i).isStar == 1) {
                                var starIcon = "select"
                            } else {
                                starIcon = "un-select"
                            }

                            if (day == 1) {
                                if (rs.rows.item(i).id <= 35) {
                                    session.append({
                                                   "schedule": from + " - " + to,
                                                   "name": rs.rows.item(i).name,
                                                   "speaker": rs.rows.item(i).speaker,
                                                   "room": getRoomCache(rs.rows.item(i).room),
                                                   "starIcon": starIcon,
                                                   "isBreak": rs.rows.item(i).isBreak,
                                                   "id": rs.rows.item(i).id

                                })

                                } else {
                                    break;
                                }
                            } else if (day == 2) {
                                if (rs.rows.item(i).id > 35 && rs.rows.item(i).id <= 70) {
                                    session.append({
                                                   "schedule": from + " - " + to,
                                                   "name": rs.rows.item(i).name,
                                                   "speaker": rs.rows.item(i).speaker,
                                                   "room": getRoomCache(rs.rows.item(i).room),
                                                   "starIcon": starIcon,
                                                   "isBreak": rs.rows.item(i).isBreak,
                                                   "id": rs.rows.item(i).id
                                })
                                }
                            } else {
                               // console.log("error");
                            }
                        }
                        res = "OK";
                    }
                }

                )
    //console.log(res);
    return res;
}

function getSessionOpenCache(index, session) {
    var db = openCache();
    var res = "Unknown";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM sessions;");
                    if (rs.rows.length > 0) {
                        for (var i = 0; i < rs.rows.length; i++) {
                            var from = timestamp2time(rs.rows.item(i).from_, "hh:mm");
                            var to = timestamp2time(rs.rows.item(i).to_, "hh:mm");
                            var day = timestamp2time(rs.rows.item(i).from_, "dddd")

                            if (rs.rows.item(i).isStar == 1) {
                                var starIcon = "select"
                            } else {
                                starIcon = "un-select"
                            }
/*
                            if (index == 12)
                                continue;
*/
                            if (rs.rows.item(i).type == index) {
                                session.append({
                                               "name": rs.rows.item(i).name,
                                               "speaker": rs.rows.item(i).speaker,
                                               "from": from,
                                               "to": to,
                                               "day": day,
                                               "room": getRoomCache(rs.rows.item(i).room),
                                               "starIcon": starIcon,
                                               "id": rs.rows.item(i).id
                            })
                            }
                        }
                        res = "OK";
                    }
                }

                )
    return res;
}

function getSessionDetailCache(id, session) {
    var db = openCache();
    var res = "Unknown";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM sessions;");
                    if (rs.rows.length > 0) {

                        for (var i = 0; i < rs.rows.length; i++) {
                            if (rs.rows.item(i).id == id) {
                                session.append({
                                               "name": rs.rows.item(i).name,
                                               "speaker": rs.rows.item(i).speaker,
                                               "speakerTitle": rs.rows.item(i).speakerTitle,
                                               "room": getRoomCache(rs.rows.item(i).room),
                                               "abstract_": rs.rows.item(i).abstract,
                                               "bio": rs.rows.item(i).bio,
                                               "id": rs.rows.item(i).id
                            })
                            }
                        }
                        res = "OK";
                    }
                }

                )
    return res;
}

function getSponsorCache(level, sponsor) {
    var db = openCache();
    var res = "Unknown";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM sponsors;");
                    if (rs.rows.length > 0) {
                        for (var i = 0; i < rs.rows.length; i++) {
                            if (rs.rows.item(i).level == level) {
                                sponsor.append({
                                               "logoUrl": rs.rows.item(i).logoUrl,
                                               "name": rs.rows.item(i).name,
                                               "desc": rs.rows.item(i).desc,
                                               "url": rs.rows.item(i).url
                            })
                            }

                        }
                        res = "OK";
                    }
                }

                )
    return res
}



function setSponsorCache(sponsor) {
    var db = openCache();
    var res = "Error";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("DELETE FROM sponsors;");
                    var count = 0;
                    for (var i = 0; i < sponsor.count; i++) {
                        rs = tx.executeSql("INSERT OR REPLACE INTO sponsors VALUES (?,?,?,?,?);",
                                           [sponsor.get(i).level, sponsor.get(i).desc, sponsor.get(i).logoUrl, sponsor.get(i).name, sponsor.get(i).url]);
                        count += rs.rowsAffected;
                    }
                    if (count > 0)
                        res = "OK"
                }

                )
    return res;
}

function getTypeCache(type) {
    var db = openCache();
    var res = "Unknown";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM types;");

                    if (rs.rows.length > 0) {
                        for (var i = 0; i < rs.rows.length; i++) {
                            type.append({"type": rs.rows.item(i).type});
                        }
                        res = "OK";
                    }
                }

                )
    return res;
}

function setTypeCache(type) {
    var db = openCache();
    var res = "Error";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("DELETE FROM types;");
                    var count = 0;
                    for (var i = 0; i < type.count; i++) {
                        rs = tx.executeSql("INSERT OR REPLACE INTO types VALUES (?);", [type.get(i).type]);
                        count += rs.rowsAffected;
                    }
                    if (count > 0)
                        res = "OK"
                }

                )
    return res;
}

function getRoomCache(index) {
    var db = openCache();
    var res = "Unknown";
    var arr = new Array(5);

    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM rooms;");

                    if (rs.rows.length > 0) {
                        for (var i = 0; i < rs.rows.length; i++) {
                            arr[i] = rs.rows.item(i).room;
                        }
                        res = "OK";
                    }
                }

                )
    return arr[index];
}

function setRoomCache(room) {
    var db = openCache();
    var res = "Error";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("DELETE FROM rooms;");
                    var count = 0;
                    for (var i = 0; i < room.count; i++) {
                        rs = tx.executeSql("INSERT OR REPLACE INTO rooms VALUES (?);", [room.get(i).room]);
                        count += rs.rowsAffected;
                    }
                    if (count > 0)
                        res = "OK"
                }

                )
    return res;
}

function updateSessionCache(session) {
    var db = openCache();
    var res = "Error";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM sessions");
                    var count = 0;
                    for (var i = 0; i < session.count; i++) {
                        rs = tx.executeSql("UPDATE sessions SET abstract=? WHERE abstract=?;", [session.get(i).abstract, session.get(i).abstract]);
                        rs = tx.executeSql("UPDATE sessions SET bio=? WHERE bio=?;", [session.get(i).bio, session.get(i).bio]);
                        rs = tx.executeSql("UPDATE sessions SET from_=? WHERE from_=?;", [session.get(i).from_, session.get(i).from_]);
                        rs = tx.executeSql("UPDATE sessions SET id=? WHERE id=?;", [session.get(i).id, session.get(i).id]);
                        rs = tx.executeSql("UPDATE sessions SET isBreak=? WHERE isBreak=?;", [session.get(i).isBreak, session.get(i).isBreak]);
                        rs = tx.executeSql("UPDATE sessions SET lang=? WHERE lang=?;", [session.get(i).lang, session.get(i).lang]);
                        rs = tx.executeSql("UPDATE sessions SET name=? WHERE name=?;", [session.get(i).name, session.get(i).name]);
                        rs = tx.executeSql("UPDATE sessions SET room=? WHERE room=?;", [session.get(i).room, session.get(i).room]);
                        rs = tx.executeSql("UPDATE sessions SET speaker=? WHERE speaker=?;", [session.get(i).speaker, session.get(i).speaker]);
                        rs = tx.executeSql("UPDATE sessions SET speakerTitle=? WHERE speakerTitle=?;", [session.get(i).speakerTitle, session.get(i).speakerTitle]);
                        rs = tx.executeSql("UPDATE sessions SET to_=? WHERE to_=?;", [session.get(i).to_, session.get(i).to_]);
                        rs = tx.executeSql("UPDATE sessions SET type=? WHERE type=?;", [session.get(i).type, session.get(i).type]);

                        count += rs.rowsAffected;
                    }

                    if (count > 0)
                        res = "OK";
                }

                )
    return res;
}

function getSessionCache(session) {
    var db = openCache();
    var res = "Unknown";
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("SELECT * FROM sessions");
                    if (rs.rows.length > 0) {
                        for (var i = 0; i < rs.rows.lenght; i++) {
                            session.append({"name": rs.rows.item(i).name,
                                           "speaker": rs.rows.item(i).speaker,
                                           "speakerTitle": rs.rows.item(i).speakerTitle,
                                           "room": rs.rows.item(i).room,
                                           "abstract": rs.rows.item(i).abstract,
                                           "bio": rs.rows.item(i).bio,
                                           "from": rs.rows.item(i).from_,
                                           "to": rs.rows.item(i).to_});
                        }
                        res = "OK";
                    }
                }

                )
    return res;
}

function setSessionCache(session) {
    var db = openCache();
    var res = "Error"
    db.transaction(
                function(tx) {
                    var rs = tx.executeSql("DELETE FROM sessions");
                    var count = 0;
                    for (var i = 0; i < session.count; i++) {
                        rs = tx.executeSql("INSERT OR REPLACE INTO sessions VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?);",
                                           [session.get(i).abstract, session.get(i).bio, session.get(i).from, session.get(i).id, session.get(i).isBreak,
                                            session.get(i).lang, session.get(i).name, session.get(i).room, session.get(i).speaker, session.get(i).speakerTitle,
                                            session.get(i).to, session.get(i).type, 0]);
                        count += rs.rowsAffected;
                    }

                    if (count > 0)
                        res = "OK";
                }

                )
    return res;
}
